package org.lisy.java.io;

import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;

/**
 * EasyExcel 文件生成
 * 
 * @author lisy
 */
public class EasyExcelOperate {
	
	// 目标 EXCEL 文件目录
	private final static String TARGET_EXCEL_FILE_DIR = "./deploy/target/easyexcel";
	// 目标 EXCEL 文件
	private final static String TARGET_EXCEL_FILE = "target.xlsx";
	// sheet 页条数
	private final static int SHEET_COUNT = 5000;
	// 批次写入条数
	private final static int WRITE_COUNT = 500;

	public static void main(String[] args) {
		// 获取标题
		List<List<String>> head = getHead();
		
		// 获取数据
		List<List<String>> data = getData();
		
		// 准备生成文件
		writeFile(head, data);
	}

	/**
	 * 获取文件标题
	 * @return
	 */
	private static List<List<String>> getHead() {
		List<List<String>> head = new ArrayList<List<String>>();
		for (int i = 0; i < 10; i++) {
			List<String> temp = new ArrayList<String>();
			temp.add("title"+(i+1));
			head.add(temp);
		}
		return head;
	}

	/**
	 * 获取文件数据
	 * @return
	 */
	private static List<List<String>> getData() {
		List<List<String>> data = new ArrayList<List<String>>();
		for (int i = 0; i < 16800; i++) {
			List<String> row = new ArrayList<String>();
			for (int j = 0; j < 10; j++) {
				if (i < 10 && j == 9) {
					row.add("1");
				} else {
					row.add((i + 1) + "_" + (j + 1));
				}
			}
			data.add(row);
		}
		return data;
	}

	/**
	 * 获取自定义样式集合
	 * @return
	 */
	private static HashMap<Integer, List<Integer>> getMap(List<List<String>> subList, int sheetRow, int count) {
		HashMap<Integer, List<Integer>> map = new HashMap<Integer, List<Integer>>();
		for (int i = 0; i < subList.size(); i++) {
			ArrayList<Integer> indexList = new ArrayList<Integer>();
			List<String> list = subList.get(i);
			if ("1".equals(list.get(list.size() - 1))) {
				for (int j = 0; j < list.size(); j++) {
					indexList.add(j);
				}
			}
			if (indexList.size() > 0) {
				map.put((sheetRow - count + i + 1), indexList);
			}
		}
		return map;
	}

	/**
	 * 生成文件
	 * @param head
	 * @param date
	 */
	private static void writeFile(List<List<String>> head, List<List<String>> data) {
		long startTime = System.currentTimeMillis();
		String fileName = TARGET_EXCEL_FILE_DIR + File.separator + TARGET_EXCEL_FILE;
		File file = new File(fileName);
		if (!file.exists()) {
			// 如果上级不存在，就会自动创建
			file.getParentFile().mkdirs();
		}
		// 自定义拦截器
        MyCellWriteHandler writeHandler = new MyCellWriteHandler();
        writeHandler.setColorIndex(IndexedColors.SEA_GREEN.getIndex());
		ExcelWriter excelWrite = EasyExcel.write(file).inMemory(true)
        		.registerWriteHandler(writeHandler).head(head).build();
		WriteSheet writeSheet = null;
		// 记录写入的次数
		int num = 0;
		int count = 0;
		int startNum = 0;
		int sheetRow = 0;
		for (int i = 0; i < data.size(); i++) {
			// 限制 sheet 页写入条数, 单 sheet 页存在文件上限
			if (num % SHEET_COUNT == 0) {
				writeSheet = EasyExcel.writerSheet("page" + (num/SHEET_COUNT + 1)).build();
				System.out.println("Create sheet: page" + (num/SHEET_COUNT + 1));
				sheetRow = 0;
			}
			num++;
			count++;
			sheetRow++;
			// 每次写一批数据, 可分开查询数据防止内存溢出
			if (count >= WRITE_COUNT || i == data.size() - 1) {
				List<List<String>> subList = data.subList(startNum, (i+1));
				HashMap<Integer, List<Integer>> map = getMap(subList, sheetRow, count);
				writeHandler.setMap(map);
				excelWrite.write(subList, writeSheet);
				startNum = i+1;
				System.out.println("File write " + count + " data!");
				count = 0;
			}
		}
		System.out.println("File export finish! NUM:" + num +", TC:" + (System.currentTimeMillis() - startTime));
		if (excelWrite != null) {
			excelWrite.close();
		}
	}

}

/**
 * 自定义拦截器
 */
class MyCellWriteHandler implements CellWriteHandler {
	// map key：行(head 从 0 开始) value：列集合(从 0 开始)
	private HashMap<Integer, List<Integer>> map;

	private Short colorIndex;
	
	private Font goldFont = null;
	
	private Font font = null;
	
	private CreationHelper creationHelper = null;

	public void setMap(HashMap<Integer, List<Integer>> map) {
		this.map = map;
	}

	public void setColorIndex(Short colorIndex) {
		this.colorIndex = colorIndex;
	}

	@Override
	public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
			Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {

	}

	@Override
	public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,
			Head head, Integer relativeRowIndex, Boolean isHead) {

	}

	@Override
	public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
			WriteCellData<?> cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

	}

	@Override
	public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
			List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
	    if (isHead) {
	    	
		} else {
			Sheet sheet = writeSheetHolder.getSheet();
        	Workbook workbook = sheet.getWorkbook();
			String value = cell.toString();
			// 当前行
			int rowIndex = cell.getRowIndex();
			// 当前列
		    int columnIndex = cell.getColumnIndex();
		    List<Integer> integers = map.get(rowIndex);
	        if (integers != null && integers.size() > 0 && integers.contains(columnIndex)) {
            	CellStyle cellStyle = workbook.createCellStyle();
	            if ("1".equals(value)) {
	            	if (font == null) {
	            		font = workbook.createFont();
	            		font.setColor(IndexedColors.GOLD.getIndex());
		            	font.setBold(true);
	            	}
	            	cellStyle.setFont(font);
	            }
	            cellStyle.setFillForegroundColor(colorIndex);
	            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
	            cell.setCellStyle(cellStyle);
	        } else {
	        	if (value != null && value.length() > 2) {
	        		if (creationHelper == null) {
	        			creationHelper = workbook.getCreationHelper();
	        		}
					RichTextString richString = creationHelper.createRichTextString(value);
					if (goldFont == null) {
						goldFont = workbook.createFont();
						goldFont.setColor(colorIndex);
	            	}
					richString.applyFont(0, 2, goldFont);
					cell.setCellValue(richString);
				}
	        }
		}
	}
}
